package com.desay.pangoo.itmt.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.desay.pangoo.common.lite.lang.web.BusinessException;
import com.desay.pangoo.common.lite.lang.web.PangooWebConstant;
import com.desay.pangoo.common.lite.spring.web.ControllerCommonUtils;
import com.desay.pangoo.itmt.config.TechOnlineReviewSchedule;
import com.desay.pangoo.itmt.constants.TechProjectInfoFileds;
import com.desay.pangoo.itmt.controller.vo.TechProjectInfoChangeVo;
import com.desay.pangoo.itmt.controller.vo.TechProjectInfoVo;
import com.desay.pangoo.itmt.controller.vo.TechYearQuarterVo;
import com.desay.pangoo.itmt.entity.*;
import com.desay.pangoo.itmt.enums.DataBaseTableEnum;
import com.desay.pangoo.itmt.enums.ExceptionCodeEnum;
import com.desay.pangoo.itmt.repository.*;
import com.desay.pangoo.itmt.repository.spec.ExpandCriteria;
import com.desay.pangoo.itmt.repository.spec.Restrictions;
import com.desay.pangoo.itmt.service.TechProjectInfoService;
import com.desay.pangoo.itmt.utils.CompareObjUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author uidq1163
 */
@Service
@Transactional(rollbackFor = Exception.class)
@Slf4j
public class TechProjectInfoServiceImpl implements TechProjectInfoService {

	@Autowired
	TechProjectInfoRepository techProjectInfoRepository;
	@Autowired
	TechProjectInfoHistoryRepository techProjectInfoHistoryRepository;

	@Autowired
	WorkGroupRepository workGroupRepository;
	@Autowired
	TechClassificationRepository techClassificationRepository;
	@Autowired
	TechYearQuarterRepository techYearQuarterRepository;
	@Autowired
	private TechProjectInfoChangeRepository techProjectInfoChangeRepository;

	@Autowired
	private ModifyLogServiceImpl modifyLogService;

	@Autowired
	private TechOnlineReviewRepository techOnlineReviewRepository;

	@Autowired
	private OrgChartRepository orgChartRepository;

	/**
	 * 子版本分隔符号
	 */
	private static final String SUB_VERSION_POINT = "\\.";

	@Autowired
	private TechOnlineReviewSchedule techOnlineReviewSchedule;

	@Autowired
	private ProjectBURepository projectBURepository;

	@Autowired
	private BUDivRepository bUDivRepository;

	@Override
	public Long create(TechProjectInfoVo vo) {
		String user = ControllerCommonUtils.getHeader(PangooWebConstant.HEADER_NAME_USER_FULL_NAME);
		TechProjectInfoEntity techProjectInfoEntity = new TechProjectInfoEntity();
		techProjectInfoEntity.setCreator(user);
		if (null != vo.getTechClassId()) {
			Optional<TechClassificationEntity> optionClass = techClassificationRepository.findById(vo.getTechClassId());
			if (optionClass.isPresent()) {
				techProjectInfoEntity.setTechClass(optionClass.get());
			} else {
				throw new BusinessException(ExceptionCodeEnum.TECH_CLASSIFICATION_NOT_EXIST);
			}
		} else {
			throw new BusinessException(ExceptionCodeEnum.TECH_CLASSIFICATION_NOT_EMPTY);
		}

		// 项目名称，子项目不能重复
		ExpandCriteria<TechProjectInfoEntity> c = new ExpandCriteria<>();
		c.add(Restrictions.eq("techClass.id", vo.getTechClassId(), true));
		c.add(Restrictions.eq(TechProjectInfoFileds.TECHNAME, vo.getTechName(), true));
		c.add(Restrictions.eq(TechProjectInfoFileds.SUBSIDIARY, vo.getSubsidiary(), true));
		c.add(Restrictions.eq("parent.id", vo.getParentId(), true));
		List<TechProjectInfoEntity> listExist = techProjectInfoRepository.findAll(c);
		if (CollUtil.isNotEmpty(listExist)) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_EXIST);
		}

		BeanUtils.copyProperties(vo, techProjectInfoEntity);
		// 专利信息
		if (null != vo.getPatent()) {
			PatentEntity patentEntity = new PatentEntity();
			BeanUtils.copyProperties(vo.getPatent(), patentEntity);
			techProjectInfoEntity.setPatent(patentEntity);
		}
		// Trinity项目信息
		if (null != vo.getProject()) {
			ProjectEntity projectEntity = new ProjectEntity();
			BeanUtils.copyProperties(vo.getProject(), projectEntity);
			techProjectInfoEntity.setProject(projectEntity);
		}
		// 技术编号：自动生成
		WorkGroupEntity workGroup = techProjectInfoEntity.getTechClass().getWorkGroup();
		techProjectInfoEntity.setTechPtn(getProjectPtn(workGroup, vo.getTechName(), vo.getSubsidiary()));

		// 项目时间线信息
		if (CollUtil.isNotEmpty(vo.getTechYearQuarters())) {
			Set<TechYearQuarterEntity> yearsData = new HashSet<>();
			for (TechYearQuarterVo techYearQuarterVo : vo.getTechYearQuarters()) {
				TechYearQuarterEntity techYearQuarterEntity = new TechYearQuarterEntity();
				BeanUtils.copyProperties(techYearQuarterVo, techYearQuarterEntity);
				techYearQuarterEntity
						.setYearQuarter(techYearQuarterVo.getYear() + "0" + techYearQuarterVo.getQuarter());
				techYearQuarterEntity.setCreator(user);
				yearsData.add(techYearQuarterEntity);
			}
			techProjectInfoEntity.setTechYearQuarters(yearsData);
		}

		if (null != vo.getParentId()) {
			Optional<TechProjectInfoEntity> opiton = techProjectInfoRepository.findById(vo.getParentId());
			if (!opiton.isPresent()) {
				throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
			}
			techProjectInfoEntity.setParent(opiton.get());
		}
		// 关联提案申请
		if (null != vo.getTechOnlineReviewId()) {
			Optional<TechOnlineReviewEntity> option = techOnlineReviewRepository.findById(vo.getTechOnlineReviewId());
			if (option.isPresent()) {
				techProjectInfoEntity.setTechOnlineReview(option.get());
			}
		}
		techProjectInfoEntity.setUpdator(user);

		// 关联平台信息
		if (CollUtil.isNotEmpty(vo.getPlatformIds())) {
			Set<ProjectBUEntity> platforms = vo.getPlatformIds().stream().map(p -> {
				Optional<ProjectBUEntity> option = projectBURepository.findById(p);
				return option.get();
			}).collect(Collectors.toSet());
			techProjectInfoEntity.setPlatforms(platforms);
		}

		// 相关BU
		ExpandCriteria<BUDivEntity> buc = new ExpandCriteria<>();
		if (CollUtil.isNotEmpty(vo.getRelevanceBuss())) {
			buc.add(Restrictions.in("id", vo.getRelevanceBuss(), true));
			List<BUDivEntity> bus = bUDivRepository.findAll(buc);
			techProjectInfoEntity.setRelevanceBuss(bus.stream().collect(Collectors.toSet()));
		}
		// 开发责任主体
		if (CollUtil.isNotEmpty(vo.getDutyBuss())) {
			buc = new ExpandCriteria<>();
			buc.add(Restrictions.in("id", vo.getDutyBuss(), true));
			List<BUDivEntity> bus = bUDivRepository.findAll(buc);
			techProjectInfoEntity.setDutyBuss(bus.stream().collect(Collectors.toSet()));
		}

		// 技术交付状态
		if (StringUtils.isEmpty(techProjectInfoEntity.getDeliveryReview())) {
			techProjectInfoEntity.setDeliveryReview("-");
		}
		// budgeting 总预算
		// planBudgeting 课题规划费用预算
		// resourceNeeded 资源需求(人月）*40000
		techProjectInfoEntity.setBudgeting(
				(null == techProjectInfoEntity.getPlanBudgeting() ? 0 : techProjectInfoEntity.getPlanBudgeting())
						+ 40000 * (null == techProjectInfoEntity.getResourceNeeded() ? 0
								: techProjectInfoEntity.getResourceNeeded()));
		techProjectInfoEntity = techProjectInfoRepository.save(techProjectInfoEntity);

		// 增加新建日志记录
		addUpateLog(new TechProjectInfoEntity(), techProjectInfoEntity);
		return techProjectInfoEntity.getId();
	}

	/**
	 * 根据工作组名称自动生成技术编号
	 *
	 * @param workGroup
	 * @param techName
	 * @param subsidiary
	 * @return
	 */
	private String getProjectPtn(WorkGroupEntity workGroup, String techName, String subsidiary) {

		String code = workGroup.getPtnHeader();
		if (StringUtils.isEmpty(code)) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_GROUP_NOT_EXIST);
		}
		// 年份：2022->22
		String year = String.valueOf(DateUtil.thisYear()).substring(2);
		// 所在组的序号
		code = code.concat(year);
		List<TechProjectInfoEntity> list = null;
		Integer subsidiaryNo = null;
		Integer ptnNo = null;
		if (StringUtils.isNotEmpty(subsidiary)) {
			list = techProjectInfoRepository.findByTechNameAndTechPtnLikeOrderByTechPtnDesc(techName, code.concat("%"));
			if (CollUtil.isNotEmpty(list)) {
				// 已经存在子项目信息
				String ptn = list.get(0).getTechPtn();
				if (ptn.contains(".")) {
					// 说明是有子技术
					String[] ptns = ptn.split(SUB_VERSION_POINT);
					subsidiaryNo = Integer.valueOf(ptns[1]) + 1;
					code = ptns[0].concat(".").concat(StringUtils.leftPad("" + subsidiaryNo, 2, "0"));
				} else {
					// 只有项目主版本号：理论上不存在这种情况
					code = ptn.concat(".").concat("01");
				}
				return code;
			} else {
				// 不存在子项目信息
				list = techProjectInfoRepository.findByTechPtnLikeOrderByTechPtnDesc(code.concat("%"));
				if (CollUtil.isNotEmpty(list)) {
					// 项目信息存在，项目编号自增1
					String ptn = list.get(0).getTechPtn();
					if (ptn.contains(".")) {
						// 说明是有子技术
						String[] ptns = ptn.split(SUB_VERSION_POINT);
						ptn = ptns[0];
					}
					ptn = ptn.substring(ptn.length() - 3);
					// 项目顺序号
					ptnNo = Integer.valueOf(ptn) + 1;
					code = code.concat(StringUtils.leftPad("" + ptnNo, 3, "0"));
					// 增加子版本号
					code = code.concat(".01");
					return code;
				} else {
					code = code.concat("001").concat(".01");
					return code;
				}
			}
		} else {
			list = techProjectInfoRepository.findByTechPtnLikeOrderByTechPtnDesc(code.concat("%"));
			if (CollUtil.isNotEmpty(list)) {
				String ptn = list.get(0).getTechPtn();
				if (ptn.contains(".")) {
					// 说明是有子技术
					String[] ptns = ptn.split(SUB_VERSION_POINT);
					ptn = ptns[0];
				}
				ptn = ptn.substring(ptn.length() - 3);
				ptnNo = Integer.valueOf(ptn) + 1;
				code = code.concat(StringUtils.leftPad("" + ptnNo, 3, "0"));
				return code;
			} else {
				return code.concat("001");
			}
		}
	}

	@Override
	public void delete(Long id) {
		Optional<TechProjectInfoEntity> opiton = techProjectInfoRepository.findById(id);
		if (!opiton.isPresent()) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
		}
		techProjectInfoRepository.deleteById(id);
	}

	@Override
	public void update(Long id, TechProjectInfoVo vo) {
		String user = ControllerCommonUtils.getHeader(PangooWebConstant.HEADER_NAME_USER_FULL_NAME);
		Optional<TechProjectInfoEntity> option = techProjectInfoRepository.findById(id);
		if (option.isPresent()) {
			// 项目名称，子项目不能重复
			ExpandCriteria<TechProjectInfoEntity> c = new ExpandCriteria<>();
			c.add(Restrictions.ne("id", id, true));
			c.add(Restrictions.eq("techClass.id", vo.getTechClassId(), true));
			c.add(Restrictions.eq(TechProjectInfoFileds.TECHNAME, vo.getTechName(), true));
			c.add(Restrictions.eq(TechProjectInfoFileds.SUBSIDIARY, vo.getSubsidiary(), true));
			if (null != option.get().getParent()) {
				c.add(Restrictions.eq("parent.id", option.get().getParent().getId(), true));
			}
			List<TechProjectInfoEntity> listExist = techProjectInfoRepository.findAll(c);
			if (CollUtil.isNotEmpty(listExist)) {
				throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_EXIST);
			}
			TechProjectInfoEntity techProjectInfoEntity = option.get();
			if (!vo.getVersion().equals(techProjectInfoEntity.getVersion())) {
				throw new BusinessException(ExceptionCodeEnum.DATA_VERSION_ERROR);
			}
			TechProjectInfoEntity oldEntity = CompareObjUtil.clone(techProjectInfoEntity);
			// 取出所有关联计划ID
			List<Long> ids = techProjectInfoEntity.getTechYearQuarters().stream().map(t -> t.getId())
					.collect(Collectors.toList());
			// 为null字段不需要覆盖修改
			BeanUtils.copyProperties(vo, techProjectInfoEntity, ControllerCommonUtils.getNullPropertyNames(vo));

			// 项目时间线信息
			if (CollUtil.isNotEmpty(vo.getTechYearQuarters())) {
				List<Long> existIds = new ArrayList<>();
				Set<TechYearQuarterEntity> yearsData = new HashSet<>();
				for (TechYearQuarterVo techYearQuarterVo : vo.getTechYearQuarters()) {
					TechYearQuarterEntity techYearQuarterEntity = null;
					if (null != techYearQuarterVo.getId()) {
						existIds.add(techYearQuarterVo.getId());
						Optional<TechYearQuarterEntity> yearsOption = techYearQuarterRepository
								.findById(techYearQuarterVo.getId());
						if (yearsOption.isPresent()) {
							techYearQuarterEntity = yearsOption.get();
						} else {
							techYearQuarterEntity = new TechYearQuarterEntity();
						}
					} else {
						techYearQuarterEntity = new TechYearQuarterEntity();
					}
					BeanUtils.copyProperties(techYearQuarterVo, techYearQuarterEntity, "id");
					techYearQuarterEntity
							.setYearQuarter(techYearQuarterVo.getYear() + "0" + techYearQuarterVo.getQuarter());
					techYearQuarterEntity.setCreator(user);
					yearsData.add(techYearQuarterEntity);
				}
				techProjectInfoEntity.setTechYearQuarters(yearsData);
				ids.removeAll(existIds);
			} else {
				techProjectInfoEntity.setTechYearQuarters(null);
			}
			if (CollUtil.isNotEmpty(ids)) {
				// 删除多余的ID
				techYearQuarterRepository.deleteAllById(ids);
			}

			techProjectInfoEntity.setUpdator(user);
			// 关联平台信息
			if (CollUtil.isNotEmpty(vo.getPlatformIds())) {
				Set<ProjectBUEntity> platforms = vo.getPlatformIds().stream().map(p -> {
					Optional<ProjectBUEntity> projectOption = projectBURepository.findById(p);
					return projectOption.get();
				}).collect(Collectors.toSet());
				techProjectInfoEntity.setPlatforms(platforms);
			} else {
				techProjectInfoEntity.setPlatforms(null);
			}

			// 相关BU
			ExpandCriteria<BUDivEntity> buc = new ExpandCriteria<>();
			if (CollUtil.isNotEmpty(vo.getRelevanceBuss())) {
				buc.add(Restrictions.in("id", vo.getRelevanceBuss(), true));
				List<BUDivEntity> bus = bUDivRepository.findAll(buc);
				techProjectInfoEntity.setRelevanceBuss(bus.stream().collect(Collectors.toSet()));
			} else {
				techProjectInfoEntity.setRelevanceBuss(null);
			}
			// 开发责任主体
			if (CollUtil.isNotEmpty(vo.getDutyBuss())) {
				buc = new ExpandCriteria<>();
				buc.add(Restrictions.in("id", vo.getDutyBuss(), true));
				List<BUDivEntity> bus = bUDivRepository.findAll(buc);
				techProjectInfoEntity.setDutyBuss(bus.stream().collect(Collectors.toSet()));
			} else {
				techProjectInfoEntity.setDutyBuss(null);
			}

			// 专利信息
			if (null != vo.getPatent()) {
				PatentEntity patentEntity = new PatentEntity();
				BeanUtils.copyProperties(vo.getPatent(), patentEntity);
				techProjectInfoEntity.setPatent(patentEntity);
			} else {
				techProjectInfoEntity.setPatent(null);
			}
			// Trinity项目信息
			if (null != vo.getProject()) {
				ProjectEntity projectEntity = new ProjectEntity();
				BeanUtils.copyProperties(vo.getProject(), projectEntity);
				techProjectInfoEntity.setProject(projectEntity);
			} else {
				techProjectInfoEntity.setProject(null);
			}
			// 技术交付状态
			if (StringUtils.isEmpty(techProjectInfoEntity.getDeliveryReview())) {
				techProjectInfoEntity.setDeliveryReview("-");
			}
			// budgeting 总预算
			// planBudgeting 课题规划费用预算
			// resourceNeeded 资源需求(人月）*40000
			techProjectInfoEntity.setBudgeting(
					(null == techProjectInfoEntity.getPlanBudgeting() ? 0 : techProjectInfoEntity.getPlanBudgeting())
							+ 40000 * (null == techProjectInfoEntity.getResourceNeeded() ? 0
									: techProjectInfoEntity.getResourceNeeded()));

			techProjectInfoRepository.save(techProjectInfoEntity);

			// 保留历史记录
			addUpateLog(oldEntity, techProjectInfoEntity);
		} else {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
		}
	}

	/**
	 * 保留变更记录
	 *
	 * @param oldEntity
	 *            更新前entity
	 * @param updatedEntity
	 *            更新后entity
	 */
	private void addUpateLog(TechProjectInfoEntity oldEntity, TechProjectInfoEntity updatedEntity) {
		// 保留历史记录
		String user = ControllerCommonUtils.getHeader(PangooWebConstant.HEADER_NAME_USER_FULL_NAME);
		TechProjectInfoHistoryEntity history = new TechProjectInfoHistoryEntity();
		BeanUtils.copyProperties(oldEntity, history, "id", "techClass");
		history.setParentId(oldEntity.getId());
		history.setTechClass(updatedEntity.getTechClass());
		history.setCreator(user);
		if (CollUtil.isNotEmpty(oldEntity.getTechYearQuarters())) {
			Set<TechYearQuarterHistoryEntity> techYearQuartersHistory = oldEntity.getTechYearQuarters().stream()
					.map(y -> {
						TechYearQuarterHistoryEntity historyY = new TechYearQuarterHistoryEntity();
						BeanUtils.copyProperties(y, historyY, "id", "techProject");
						return historyY;
					}).collect(Collectors.toSet());
			history.setTechYearQuarters(techYearQuartersHistory);
		}

		try {
			TechProjectInfoEntity newEntity = CompareObjUtil.clone(updatedEntity);
			if (null != newEntity) {
				// 保存修改日志
				// 不需要比较的字段（分类）
				newEntity.setTechClass(null);
				newEntity.setParent(null);
				newEntity.setChildren(null);
				newEntity.setPlatforms(null);
				oldEntity.setTechClass(null);
				oldEntity.setParent(null);
				oldEntity.setChildren(null);
				oldEntity.setPlatforms(null);

				if (CollUtil.isNotEmpty(newEntity.getTechYearQuarters())) {
					newEntity.getTechYearQuarters().forEach(a -> a.setTechProject(null));
				}
				if (CollUtil.isNotEmpty(oldEntity.getTechYearQuarters())) {
					oldEntity.getTechYearQuarters().forEach(a -> a.setTechProject(null));
				}
				List<?> diffs = CompareObjUtil.compareObj(oldEntity, newEntity);
				if (CollUtil.isNotEmpty(diffs)) {
					ModifyLogEntity modifyLogEntity = new ModifyLogEntity();
					modifyLogEntity.setAssociatedId(newEntity.getId());
					modifyLogEntity.setTechPtn(newEntity.getTechPtn());
					modifyLogEntity.setAssociatedTable(DataBaseTableEnum.TECH_PROJECT_INFO.getName());
					modifyLogEntity.setDetails(JSON.toJSONString(diffs, SerializerFeature.WriteMapNullValue));
					modifyLogService.saveLog(modifyLogEntity);
					// 只记录数据有变化的历史记录
					if (null != oldEntity.getId()) {
						techProjectInfoHistoryRepository.save(history);
						// 课题变更
						techOnlineReviewSchedule.dspProposal(false, updatedEntity);
					} else {
						// 推送邮件给TSP成员
						// 新增课题
						techOnlineReviewSchedule.dspProposal(true, updatedEntity);
					}
				}
			}
		} catch (Exception e) {
			log.error("写入数据集log失败：{}", e);
		}
	}

	@Override
	public Page<TechProjectInfoEntity> getList(Integer pageSize, Integer pageNo, List<Long> techClassId,
			List<Long> workGroupId, String techPtn, List<String> researchForms, String techName, String subsidiary,
			String description, List<Integer> techLevel, List<String> priority, String quantitativeIndicators,
			List<String> tMTApproval, List<String> maturity, List<String> requirementSource, String developedBy,
			List<Long> relevanceBuss, List<Long> dutyBuss, String toolsEquipment, Float minResourceNeeded,
			Float maxResourceNeeded, Boolean isAll, List<Long> platforms, List<Long> years, List<String> deliveryReview,
			Integer status, Boolean isMajorProject) {
		ExpandCriteria<TechProjectInfoEntity> c = new ExpandCriteria<>();
		//
		c.add(Restrictions.eq("techYearQuarters.status", status, true));
		// 年
		c.add(Restrictions.in("techYearQuarters.year", years, true));
		// 根据组查询
		c.add(Restrictions.in("techClass.id", techClassId, true));
		c.add(Restrictions.in("deliveryReview", deliveryReview, true));

		c.add(Restrictions.in("techClass.workGroup.id", workGroupId, true));
		// 技术编号：techPtn
		c.add(Restrictions.like(TechProjectInfoFileds.TECHPTN, techPtn, true));
		// 研究形式
		c.add(Restrictions.in("researchForms", researchForms, true));
		// 技术名称
		c.add(Restrictions.like(TechProjectInfoFileds.TECHNAME, techName, true));
		// 子技术
		c.add(Restrictions.like(TechProjectInfoFileds.SUBSIDIARY, subsidiary, true));
		// 说明
		c.add(Restrictions.like("description", description, true));
		// 技术等级
		c.add(Restrictions.in("techLevel", techLevel, true));
		// 重点项目
		c.add(Restrictions.eq("isMajorProject", isMajorProject, true));
		// 优先级
		c.add(Restrictions.in(TechProjectInfoFileds.PRIORITY, priority, true));
		// 量化指标
		c.add(Restrictions.like("quantitativeIndicators", quantitativeIndicators, true));
		// TMT 评审
		c.add(Restrictions.in("tMTApproval", tMTApproval, true));
		// 技术状态
		if (CollUtil.isNotEmpty(maturity)) {
			c.add(Restrictions.in(TechProjectInfoFileds.MATURITY, maturity, true));
		} else {
			// 不筛选技术状态的时候：自动隐藏cancelled的项目
			c.add(Restrictions.or(Restrictions.isNull(TechProjectInfoFileds.MATURITY),
					Restrictions.ne(TechProjectInfoFileds.MATURITY, "cancelled", true)));
		}
		// 需求来源
		c.add(Restrictions.in("requirementSource", requirementSource, true));
		// 开发负责人
		c.add(Restrictions.like("developedBy", developedBy, true));
		// 相关BU（调整后字段）
		c.add(Restrictions.in("relevanceBuss.id", relevanceBuss, true));
		// 开发责任主体
		c.add(Restrictions.in("dutyBuss.id", dutyBuss, true));
		// 产品平台
		c.add(Restrictions.in("platforms.id", platforms, true));
		// 设备需求
		c.add(Restrictions.like("toolsEquipment", toolsEquipment, true));
		// 资源需求(人月）
		c.add(Restrictions.gte("resourceNeeded", minResourceNeeded, true));
		c.add(Restrictions.lte("resourceNeeded", maxResourceNeeded, true));
		if (Boolean.FALSE.equals(isAll)) {
			// 取近五年的数据
			List<Long> ids = techProjectInfoRepository.getLately5YearsPprojectIds();
			c.add(Restrictions.in("id", ids, true));
		}
		List<Order> orders = new ArrayList<>();
		Order order = new Order(Sort.Direction.DESC, "createTime");
		Order order2 = new Order(Sort.Direction.ASC, "techPtn");
		orders.add(order);
		orders.add(order2);
		Pageable pageReq = PageRequest.of(pageNo - 1, pageSize, Sort.by(orders));
		return techProjectInfoRepository.findAll(c, pageReq);
	}

	@Override
	public TechProjectInfoEntity getById(Long id) {
		Optional<TechProjectInfoEntity> opiton = techProjectInfoRepository.findById(id);
		if (!opiton.isPresent()) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
		}
		return opiton.get();
	}

	@Override
	public Object uploadExcelFile(MultipartFile file) throws IOException {
		ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
		List<String> sheetNames = reader.getSheetNames();

		for (String groupName : sheetNames) {
			WorkGroupEntity group = workGroupRepository.findByWorkGroupName(groupName);
			if (null == group) {
				group = new WorkGroupEntity();
				group.setWorkGroupName(groupName);
				group.setPtnHeader(groupName);
				workGroupRepository.save(group);
			}
			reader = ExcelUtil.getReader(file.getInputStream(), groupName);
			List<Map<String, Object>> datas = reader.readAll();
			for (Map<String, Object> map : datas) {
				TechProjectInfoEntity techProjectInfoEntity = new TechProjectInfoEntity();

				if (null == map.get("Classification") || null == map.get("Technology name")) {
					continue;
				}
				String classificationName = map.get("Classification").toString();

				ExpandCriteria<TechClassificationEntity> c = new ExpandCriteria<>();
				c.add(Restrictions.eq("workGroup.id", group.getId(), true));
				c.add(Restrictions.eq("classificationName", classificationName, true));
				List<TechClassificationEntity> listExist = techClassificationRepository.findAll(c);
				TechClassificationEntity techClassificationEntity = null;
				if (CollUtil.isNotEmpty(listExist)) {
					techClassificationEntity = listExist.get(0);
				} else {
					techClassificationEntity = new TechClassificationEntity();
					techClassificationEntity.setClassificationName(classificationName);
					techClassificationEntity.setWorkGroup(group);
					techClassificationRepository.save(techClassificationEntity);
				}
				techProjectInfoEntity.setTechClass(techClassificationEntity);

				if (null != map.get("PTN")) {
					techProjectInfoEntity.setTechPtn(map.get("PTN").toString());
				}
				if (null != map.get("Research forms")) {
					techProjectInfoEntity.setResearchForms(map.get("Research forms").toString());
				} else {
					techProjectInfoEntity.setResearchForms("RESEARCH");
				}
				if (null != map.get("Technology name")) {
					techProjectInfoEntity.setTechName(map.get("Technology name").toString());
				}
				if (null != map.get("Subsidiary")) {
					techProjectInfoEntity.setSubsidiary(map.get("Subsidiary").toString());
				}
				if (null != map.get("Description")) {
					techProjectInfoEntity.setDescription(map.get("Description").toString());
				}
				// 核心技术：NO
				// 技术等级:核心:1，关键:2，通用:3，一般:4'
				if (null != map.get("KeyTec") && map.get("KeyTec").toString().equalsIgnoreCase("yes")) {
					techProjectInfoEntity.setTechLevel(1);
				} else {
					techProjectInfoEntity.setTechLevel(4);
				}
				if (null != map.get("priority") && StringUtils.isNotEmpty(map.get("priority").toString())) {
					techProjectInfoEntity.setPriority(map.get("priority").toString());
				} else {
					techProjectInfoEntity.setPriority("MID");
				}

				// I ->AF
				// AG=, AH=, AI=, AJ=, AK=, AL=, AM=, AN
				if (null != map.get("Quantitative indicators")) {
					techProjectInfoEntity.setQuantitativeIndicators(map.get("Quantitative indicators").toString());
				}
				if (null != map.get("TMT Approval")) {
					techProjectInfoEntity.setTMTApproval(map.get("TMT Approval").toString());
				} else {
					techProjectInfoEntity.setTMTApproval("NA");
				}
				if (null != map.get("Maturity") && StringUtils.isNotEmpty(map.get("Maturity").toString())) {
					techProjectInfoEntity.setMaturity(map.get("Maturity").toString());
				} else {
					techProjectInfoEntity.setMaturity("PROPOSAL");
				}
				if (null != map.get("Requirement Source")
						&& StringUtils.isNotEmpty(map.get("Requirement Source").toString())) {
					techProjectInfoEntity.setRequirementSource(map.get("Requirement Source").toString());
				} else {
					techProjectInfoEntity.setRequirementSource("MARKET INSIGHT/TECH INFO LIST");
				}
				if (null != map.get("Developed By")) {

					techProjectInfoEntity.setDevelopedBy(map.get("Developed By").toString());
				}
				if (null != map.get("Tools and Equipment")) {

					techProjectInfoEntity.setToolsEquipment(map.get("Tools and Equipment").toString());
				}
				if (null != map.get("Resource Needed") && StringUtils.isNotEmpty(map.get("Resource Needed").toString())
						&& !map.get("Resource Needed").toString().equals("NA")) {
					techProjectInfoEntity.setResourceNeeded(Float.valueOf(map.get("Resource Needed").toString()));
				}

				// 2021 I=ON, J=ON, K=ON, L=ON,
				Set<TechYearQuarterEntity> techYearQuarters = new HashSet<>();
				if (null != map.get("2021Q1")) {
					techYearQuarters.add(setYearData(2021, 1, map.get("2021Q1").toString()));
				}
				if (null != map.get("2021Q2")) {
					techYearQuarters.add(setYearData(2021, 2, map.get("2021Q2").toString()));
				}
				if (null != map.get("2021Q3")) {
					techYearQuarters.add(setYearData(2021, 3, map.get("2021Q3").toString()));
				}
				if (null != map.get("2021Q4")) {
					techYearQuarters.add(setYearData(2021, 4, map.get("2021Q4").toString()));
				}
				// 2022 M=ON, N=ON, O=ON, P=ON,
				if (null != map.get("2022Q1")) {
					techYearQuarters.add(setYearData(2022, 1, map.get("2022Q1").toString()));
				}
				if (null != map.get("2022Q2")) {
					techYearQuarters.add(setYearData(2022, 2, map.get("2022Q2").toString()));
				}
				if (null != map.get("2022Q3")) {
					techYearQuarters.add(setYearData(2022, 3, map.get("2022Q3").toString()));
				}
				if (null != map.get("2022Q4")) {
					techYearQuarters.add(setYearData(2022, 4, map.get("2022Q4").toString()));
				}

				// 2023 Q=ON, R=ON, S=ON, T=ON,
				if (null != map.get("2023Q1")) {
					techYearQuarters.add(setYearData(2023, 1, map.get("2023Q1").toString()));
				}
				if (null != map.get("2023Q2")) {
					techYearQuarters.add(setYearData(2023, 2, map.get("2023Q2").toString()));
				}
				if (null != map.get("2023Q3")) {
					techYearQuarters.add(setYearData(2023, 3, map.get("2023Q3").toString()));
				}
				if (null != map.get("2023Q4")) {
					techYearQuarters.add(setYearData(2023, 4, map.get("2023Q4").toString()));
				}
				// 2024 U=, V=, W=, X=,

				if (null != map.get("2024Q1")) {
					techYearQuarters.add(setYearData(2024, 1, map.get("2024Q1").toString()));
				}
				if (null != map.get("2024Q2")) {
					techYearQuarters.add(setYearData(2024, 2, map.get("2024Q2").toString()));
				}
				if (null != map.get("2024Q3")) {
					techYearQuarters.add(setYearData(2024, 3, map.get("2024Q3").toString()));
				}
				if (null != map.get("2024Q4")) {
					techYearQuarters.add(setYearData(2024, 4, map.get("2024Q4").toString()));
				}

				// 2025 Y=PLAN, Z=PLAN, AA=PLAN, AB=,
				if (null != map.get("2025Q1")) {
					techYearQuarters.add(setYearData(2025, 1, map.get("2025Q1").toString()));
				}
				if (null != map.get("2025Q2")) {
					techYearQuarters.add(setYearData(2025, 2, map.get("2025Q2").toString()));
				}
				if (null != map.get("2025Q3")) {
					techYearQuarters.add(setYearData(2025, 3, map.get("2025Q3").toString()));
				}
				if (null != map.get("2025Q4")) {
					techYearQuarters.add(setYearData(2025, 4, map.get("2025Q4").toString()));
				}
				// 2026 AC=, AD=, AE=, AF=,
				if (null != map.get("2026Q1")) {
					techYearQuarters.add(setYearData(2026, 1, map.get("2026Q1").toString()));
				}
				if (null != map.get("2026Q2")) {
					techYearQuarters.add(setYearData(2026, 2, map.get("2026Q2").toString()));
				}
				if (null != map.get("2026Q3")) {
					techYearQuarters.add(setYearData(2026, 3, map.get("2026Q3").toString()));
				}
				if (null != map.get("2026Q4")) {
					techYearQuarters.add(setYearData(2026, 4, map.get("2026Q4").toString()));
				}
				techProjectInfoEntity.setTechYearQuarters(techYearQuarters);
				techProjectInfoRepository.save(techProjectInfoEntity);
			}
		}
		return null;
	}

	private TechYearQuarterEntity setYearData(Integer year, Integer quarter, String status) {
		if (StringUtils.isEmpty(status)) {
			return null;
		}
		TechYearQuarterEntity techYearQuarterEntity = new TechYearQuarterEntity();
		techYearQuarterEntity.setYear(year);
		techYearQuarterEntity.setQuarter(quarter);
		// 状态：1:plan 2:on
		if (status.equalsIgnoreCase("on")) {
			techYearQuarterEntity.setStatus(2);
		} else {
			techYearQuarterEntity.setStatus(1);
		}
		return techYearQuarterEntity;
	}

	/**
	 * 根据课题ID获取历史记录信息
	 */
	@Override
	public Page<TechProjectInfoHistoryEntity> getHistoryById(Integer pageSize, Integer pageNum, Long id) {
		ExpandCriteria<TechProjectInfoHistoryEntity> c = new ExpandCriteria<>();
		// 根据组查询
		c.add(Restrictions.eq("parentId", id, true));
		List<Order> orders = new ArrayList<>();
		Order order = new Order(Sort.Direction.DESC, "createTime");
		orders.add(order);
		Pageable pageReq = PageRequest.of(pageNum - 1, pageSize, Sort.by(orders));
		return techProjectInfoHistoryRepository.findAll(c, pageReq);
	}

	@Override
	public Long projectChange(TechProjectInfoChangeVo vo) {
		ExpandCriteria<TechProjectInfoChangeEntity> c = new ExpandCriteria<>();
		// 根据组查询
		c.add(Restrictions.eq("techProject.id", vo.getTechProjectId(), true));
		c.add(Restrictions.eq("auditStatus", 1, true));

		List<TechProjectInfoChangeEntity> list = techProjectInfoChangeRepository.findAll(c);
		// 同一个课题不能存在多少待审核的申请
		if (CollUtil.isNotEmpty(list)) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_CHANGE_EXIST);
		}

		String user = ControllerCommonUtils.getHeader(PangooWebConstant.HEADER_NAME_USER_FULL_NAME);
		TechProjectInfoChangeEntity changeEntity = new TechProjectInfoChangeEntity();
		BeanUtils.copyProperties(vo, changeEntity, "changeFields");
		changeEntity.setCreator(user);
		changeEntity.setChangeFields(JSON.toJSONString(vo.getChangeFields()));
		Optional<TechProjectInfoEntity> opiton = techProjectInfoRepository.findById(vo.getTechProjectId());
		if (!opiton.isPresent()) {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
		}
		TechProjectInfoEntity tech = opiton.get();
		changeEntity.setTechProject(tech);
		techProjectInfoChangeRepository.save(changeEntity);

		// 推送给对应工作组的ITMT Leader
		// ITMT ChairMan:1，ITMT Leader:2，ITMT SubLeader:3，ITMT成员:4
		OrgChartEntity itmtLeader = orgChartRepository.findByWorkGroupAndOrgRole(tech.getTechClass().getWorkGroup(), 2);
		if (null != itmtLeader && StringUtils.isNotEmpty(itmtLeader.getLogin())) {
			techOnlineReviewSchedule.applicationRegardingChanges(itmtLeader.getLogin(), tech, changeEntity.getId());
		}
		return changeEntity.getId();
	}

	@Override
	public void auditProjectChange(TechProjectInfoChangeVo vo, Long id) {
		String user = ControllerCommonUtils.getHeader(PangooWebConstant.HEADER_NAME_USER_FULL_NAME);
		Optional<TechProjectInfoChangeEntity> optional = techProjectInfoChangeRepository.findById(id);
		TechProjectInfoChangeEntity entity = null;
		if (optional.isPresent()) {
			entity = optional.get();
		} else {
			throw new BusinessException(ExceptionCodeEnum.TECH_PROJECT_INFO_NOT_EXIST);
		}
		// 审核状态：1待审核，2审核通过，3审核不通过
		if (vo.getAuditStatus() == 2) {
			// 修改申请变更的内容
			TechProjectInfoEntity project = entity.getTechProject();
			if (Objects.nonNull(project)) {
				TechProjectInfoEntity oldEntity = CompareObjUtil.clone(project);
				String feildValues = entity.getChangeFields();
				JSONArray fieldArray = JSON.parseArray(feildValues);
				Field[] fields = project.getClass().getDeclaredFields();
				if (CollUtil.isNotEmpty(fieldArray)) {
					fieldArray.forEach(f -> {
						JSONObject jb = (JSONObject) f;
						String key = jb.getString("field");
						if (Objects.nonNull(key)) {
							Object value = jb.get("after");
							// 时间线规划数据单独处理
							if (key.equals("techYearQuarters")) {
								JSONArray array = (JSONArray) value;
								Set<TechYearQuarterEntity> techYearQuarters = array.stream().map(q -> {
									JSONObject jObject = (JSONObject) q;
									TechYearQuarterEntity techYearQuarterEntity = new TechYearQuarterEntity();
									techYearQuarterEntity.setQuarter(jObject.getInteger("quarter"));
									techYearQuarterEntity.setStatus(jObject.getInteger("status"));
									techYearQuarterEntity.setYear(jObject.getInteger("year"));
									return techYearQuarterEntity;
								}).collect(Collectors.toSet());
								project.setTechYearQuarters(techYearQuarters);
							} else {
								// 遍历对象的属性和值 属性和值一一对应
								for (Field field : fields) {
									// 开启对象的属性访问权和修改权
									field.setAccessible(true);
									String name = field.getName();// 获取本次遍历对象的属性名称
									if (name.equals(key)) {
										try {
											field.set(project, value);
										} catch (IllegalArgumentException e) {
											log.error("name：{}---value：{}---{}", name, value, e);
										} catch (IllegalAccessException e) {
											log.error("name：{}---value：{}---{}", name, value, e);
										}
									// 结束当前循环
										break;
									}
								}
							}
						}
					});
				}
				// 审核通过者
				project.setAuditor(user);
				project.setAuditorTime(new Date());
				project.setUpdator(entity.getCreator());
				techProjectInfoRepository.save(project);
				// 添加变更记录
				addUpateLog(oldEntity, project);
			}
			entity.setAuditor(user);
			entity.setAuditStatus(vo.getAuditStatus());
			entity.setAuditTime(new Date());
			entity.setAuditComments(vo.getAuditComments());
			techProjectInfoChangeRepository.save(entity);
		}
	}

	@Override
	public Page<TechProjectInfoChangeEntity> getChangeList(Long applyId, Long techId, Integer pageSize, Integer pageNo,
			Long techClassId, Long workGroupId, String techPtn, String techName, String creator, String auditor,
			Integer auditStatus) {
		//
		ExpandCriteria<TechProjectInfoChangeEntity> c = new ExpandCriteria<>();
		c.add(Restrictions.eq("id", applyId, true));
		// 申请者
		c.add(Restrictions.like("creator", creator, true));
		// 申请状态
		c.add(Restrictions.eq("auditStatus", auditStatus, true));
		// 审核者
		c.add(Restrictions.eq("auditor", auditor, true));
		// 技术分类
		c.add(Restrictions.eq("techProject.id", techId, true));
		c.add(Restrictions.eq("techProject.techClass.id", techClassId, true));
		// 工作组
		c.add(Restrictions.eq("techProject.techClass.workGroup.id", workGroupId, true));
		// 技术编号：techPtn
		c.add(Restrictions.like("techProject.techPtn", techPtn, true));
		// 技术名称
		c.add(Restrictions.like("techProject.techName", techName, true));
		List<Order> orders = new ArrayList<>();
		Order order = new Order(Sort.Direction.DESC, "createTime");
		orders.add(order);
		Pageable pageReq = PageRequest.of(pageNo - 1, pageSize, Sort.by(orders));
		return techProjectInfoChangeRepository.findAll(c, pageReq);
	}

	/**
	 * 根据日期，获取当前日期下的提案信息 往前偏一天，保证失败的数据有机会重试
	 * 
	 * @param date
	 *            一个日期
	 * @return
	 */
	@Override
	public List<TechProjectInfoEntity> listTechProjectInfoInDate(Date date) {
		// 往前偏一天，保证失败数据重新发送给算法端
		DateTime begin = DateUtil.offset(date, DateField.DAY_OF_MONTH, -1);
		begin = DateUtil.beginOfDay(begin);
		DateTime end = DateUtil.endOfDay(date);
		// 获取日期范围内的提案信息
		return techProjectInfoRepository.findAllByCreateTimeBetween(begin, end);
	}

}
